package com.example.yangguoying.util.JDBC.JDBCAPI;


import com.example.yangguoying.util.JDBC.APIInterface.JDBCUtil;

import org.json.JSONObject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;


/**
 * <p>数据库增删盖查，创建对象使用完之后需要调用方法<b>closeAll()</b>关闭资源
 * <p>构造方法入数据库连接字符串，用户名，密码，驱动字符串
 * <p>JDBCUtilImpl(String url, String userName, String password, String className)</p>
 * <p>oracle连接字符串jdbc:oracle:thin:@localhost:1521:[实例名称]</p>
 * <P>mysql连接字符串jdbc:mysql://localhost:3306/[数据库名称]?serverTimezone=GMT%2B8"</p>
 * <p>SQLserver连接字符串jdbc:sqlserver://localhost:1433;DatabaseName=[数据库名称]</p>
 * <p>oracle驱动类名称oracle.jdbc.OracleDriver</p>
 * <p>mysql驱动类名称com.mysql.cj.jdbc.Driver</p>
 * <p>SQLSERVER驱动类名称com.microsoft.sqlserver.jdbc.SQLServerDriver</p>
 * @implSpec insertData(String sql,Object[] params)插入数据
 * <p>deleteData(String sql,Object[] params)删除数据
 * <p>updtateData(String sql,Object[] params)更新数据
 * <p>queryData(String sql,Object[] params)查询数据，返回ResultSet结果集
 * <p>queryToMap(String sql,Object[] params)查询数据，返回map集合
 * <p>queryToString(String sql,Object[] params)查询数据，返回json字符串
 *
 * @return 返回一个JDBCUtilImpl对象
 * @author 灵山猫小熊
 * */
public class JDBCUtilImpl implements JDBCUtil {

    private Connection connection = null;
    private PreparedStatement pstmt = null;
    private ResultSet res = null;
    private ResultSetMetaData mdate = null;
    /**
     * 数据库连接字符串
     * */
    private String url;
    /**
     * 数据库用户名
     * */
    private String userName;
    /**
     * 数据库用户名对应的密码
     * */
    private String password;
    /**
     * 驱动类字符串
     * */
    private String className;


    /**
     * <p>数据库增删盖查，创建对象使用完之后需要调用方法<b>closeAll()</b>关闭资源
     * <p>构造方法入数据库连接字符串，用户名，密码，驱动字符串
     * <p>JDBCUtilImpl(String url, String userName, String password, String className)</p>
     * @param url :连接字符串
     * @param userName ：数据库用户名
     * @param password ：数据库连接密码
     * @param className ：驱动类名称
     */
    public JDBCUtilImpl(String url, String userName, String password, String className) {
        super();
        this.url = url;
        this.userName = userName;
        this.password = password;
        this.className = className;
    }
    /**
     * @return 数据库连接字符串
     * */
    public String getUrl() {
        return url;
    }
    /**
     * @return 数据库连接用户名
     * */
    public String getUserName() {
        return userName;
    }
    /**
     * @return 数据库连接密码
     * */
    public String getPassword() {
        return password;
    }
    /**
     * @return 数据库驱动类名称
     * */
    public String getClassName() {
        return className;
    }

    @Override
    public int insertData(String sql,Object[] params) {

        int count = 0;
        try {
            count = getPrepareStatement(sql, params).executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {

            e.printStackTrace();
        }
        return count;

    }
    @Override
    public int deleteData(String sql,Object[] params) {

        int count = 0;
        try {
            count = getPrepareStatement(sql, params).executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {

            e.printStackTrace();
        }
        return count;
    }
    @Override
    public int updtateData(String sql,Object[] params) {

        int count = 0;
        try {
            count = getPrepareStatement(sql, params).executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {

            e.printStackTrace();
        }
        return count;
    }
    @Override
    public ResultSet queryData(String sql,Object[] params) {
        try {
            res = getPrepareStatement(sql, params).executeQuery();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
    @Override
    public Map<Object,Object> queryToMap(String sql,Object[] params) {
        res = queryData(sql, params);
        Map<Object,Object> map = new HashMap<Object,Object>();
        try {
            mdate = res.getMetaData();
            while(res.next()) {
                for (int i = 1; i <= mdate.getColumnCount(); i++) {
                    map.put(mdate.getColumnName(i), res.getObject(i));
                }
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return map;
    }
    @Override
    public String queryToString(String sql,Object[] params) {
        JSONObject json = new JSONObject(queryToMap(sql,params));
//        JSONObject json = JSONObject.fromObject(queryToMap(sql, params));
        return json.toString();
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException{
        Class.forName(className);
        connection = DriverManager.getConnection(url,userName,password);
        return connection;
    }
    private PreparedStatement getPrepareStatement(String sql,Object[] params) throws ClassNotFoundException, SQLException {
        pstmt = getConnection().prepareStatement(sql);
        if(params != null) {
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i+1, params[i]);
            }
        }
        return pstmt;
    }
    public void closeAll() {
        try {
            if(res != null)
                res.close();
            if(pstmt != null)
                pstmt.close();
            if(connection != null)
                connection.close();
        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

